from openpyxl import Workbook
from openpyxl import load_workbook
import pathlib
import os
import sqlite3
import logging
import requests
import time
import pymysql

#从Python SDK导入BOS配置管理模块以及安全认证模块
from baidubce.bce_client_configuration import BceClientConfiguration
from baidubce.auth.bce_credentials import BceCredentials
#导入BOS相关模块
from baidubce import exception
from baidubce.services import bos
from baidubce.services.bos import canned_acl
from baidubce.services.bos.bos_client import BosClient
from baidubce.services.bos import storage_class

#设置BosClient的Host，Access Key ID和Secret Access Key
bos_host = "bj.bcebos.com"
access_key_id = "e29489c96ff44d3488a719d2f78af3d6"
secret_access_key = "d6c5cf7f1d8f4123aa3a4d7e2f566397"
bucket_name='zthubo'
url="https://192.168.2.247/cjtj"

logging.basicConfig(level=logging.INFO,
                    format='%(asctime)s  %(message)s #%(levelname)s#%(process)d_%(module)s_%(funcName)s')

root=pathlib.Path(__file__).parent
db=sqlite3.connect(str(root/"result.db"))
cur = db.cursor()

conn = pymysql.connect(
    host="localhost",
    user="xjzx",
    password="",
    database="results")
cursor = conn.cursor()

def executeSql(sql):
    global db
    global cur
    try:
        cur.execute(sql)
    except :
        logging.error(sql)
        db.rollback()
    else:
        db.commit()
        return cur

for dir_path, subpaths, files in os.walk(root, True):
    for file in files:
        file=root/pathlib.Path(file)
        suf=file.suffix
        print(str(file))
        if(suf==".xlsx"):
            wb = load_workbook(str(file))
            break


'''
n=0
sql="delete from base"
cur.execute(sql)
for sheet in wb:
    for i in range(3,sheet.max_row+1):
        if(not (sheet.cell(row=i, column=2).value==None)):
            sql=r'INSERT INTO base("姓名", "语文", "数学", "英语", "物理", "化学", "小科目", "班级") VALUES ("{name}", {yw}, {sx}, {yy}, {wl}, {hx}, {xkm}, "{bj}")'.format(
                name=sheet.cell(row=i, column=2).value,
                yw=sheet.cell(row=i, column=3).value,
                sx=sheet.cell(row=i, column=4).value,
                yy=sheet.cell(row=i, column=5).value,
                wl=sheet.cell(row=i, column=6).value,
                hx=sheet.cell(row=i, column=7).value,
                xkm=sheet.cell(row=i, column=8).value,
                bj=sheet.title
            ).replace("None","null")
            n+=1
            cur.execute(sql)
        else:
            print("none")
    print(n)
    db.commit()
'''
sql="delete from base"
cursor.execute(sql)
conn.commit()
n=0
for sheet in wb:
    for i in range(3,sheet.max_row+1):
        if(not (sheet.cell(row=i, column=2).value==None)):
            sql=r'INSERT INTO base(姓名, 语文, 数学, 英语, 物理, 化学, 小科目, 班级) VALUES ("{name}", {yw}, {sx}, {yy}, {wl}, {hx}, {xkm}, "{bj}")'.format(
                name=sheet.cell(row=i, column=2).value,
                yw=sheet.cell(row=i, column=3).value,
                sx=sheet.cell(row=i, column=4).value,
                yy=sheet.cell(row=i, column=5).value,
                wl=sheet.cell(row=i, column=6).value,
                hx=sheet.cell(row=i, column=7).value,
                xkm=sheet.cell(row=i, column=8).value,
                bj=sheet.title
            ).replace("None","null")
            n+=1
            cursor.execute(sql)
        else:
            print("none")
    print(n)
    conn.commit()
t=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())


config = BceClientConfiguration(credentials=BceCredentials(access_key_id, secret_access_key), endpoint = bos_host)
bos_client = BosClient(config)
response = bos_client.list_buckets()
r = requests.get(url,verify=False)
html=r.text.replace(".php",".html")
html=html.replace(" Arthion "," Arthion </br> 更新时间： {t}".format(t=t))
bos_client.put_object_from_string(bucket_name, "index.html", html,content_type = 'text/html')

r = requests.get(url+"/Detailed.php",verify=False)
html=r.text.replace(".php",".html")
html=html.replace(" Arthion "," Arthion </br> 更新时间： {t}".format(t=t))
bos_client.put_object_from_string(bucket_name, "Detailed.html", html,content_type = 'text/html')